SymbolRiskDetailV5

V8 Message Definiton

SymbolRiskDetail records contain semi-static markup detail for SymbolRiskSummary records.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	ClientRisk
Product	SRRisk
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
accnt	VARCHAR(16)	PRI	''
tradeDate	DATE	PRI	'1900-01-01'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
clientTags	TEXT		''	optional user defined account tags eg tagtagtagtag max of 10000 total chars including seps
stkStatus	enum - StkStatus		'Hold'	Stock trading status indicator user controlledused by the HedgeTool and by trade controllers SymbolControlstkStatus
optStatus	enum - OptStatus		'Hold'	Option trading status indicator user controlledonly used by trade controllers SymbolControloptStatus
riskClass	VARCHAR(8)		''	Symbol Risk Class Code user supplied SymbolControlriskClass
theoModel	VARCHAR(16)		''	SR assigned theo model tag associated with user supplied theo surfaces AccountConfigtheoModel
theoModel2	VARCHAR(16)		''	SR assigned theo model2 tag associated with user supplied theo surfaces AccountConfigtheoModel2
hedgeDeltaRule	enum - HedgeDeltaRule		'None'	HedgeDelta Source IVol use SR implied surface sticky strike IvS use SR surface sticky delta TVol use user supplied theo surface sticky strike TvS use user supplied theo surface and atm veSlope sticky delta AccountConfighedgeDelta
holdReason	enum - HoldReason		'None'	User supplied description only informational SymbolControlholdReason
binaryDays	FLOAT		0	Fractional days 0 50 prior to expiration after which hedgeDeltas become binary 10 05 0 05 10 SymbolControlbinaryDays
ctrlUpdate	DATETIME(6)		'1900-01-01 00:00:00.000000'	last update dttm of SymbolControl record SymbolControltimestamp
periodEndTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	DTTM of the end of the current trading period
symbolType	enum - SymbolType		'None'	SymbolType Equity ETF ShortETF ADR CashIndex FutureComplex
name	VARCHAR(16)		''	Symbol namedescription
industry	INT		0
sector	VARCHAR(16)		''
beta	FLOAT		0	beta usually beta to SPX see AccountConfigbetaSource
betaSource	enum - BetaSource		'None'
stkVolume	FLOAT		0	trailing 30 day average daily stock volume
optVolume	FLOAT		0	trailing 30 day average daily option volume
tapeCode	enum - TapeCode		'None'	market data tape code
marginType	enum - MarginType		'None'	margin slide type NMSEquity 15 NMSIndex 86 NMSMedium 10
pointCurrency	enum - Currency		'None'
dAmt	FLOAT		0	amount of the next expected dividend
dDays	SMALLINT		0	days to next expected dividend negative indicates days from a recent exdate
eDays	SMALLINT		0	days to next expected earnings negative indicates days from a recent announcement
locateQuan	INT		0	starting locate quantity in this symbol befores trades
availableLocateQuan	INT		0	available equity locate quantity in this symbol after trades
gcFlag	enum - YesNo		'None'	is this symbol a general collateral name no special borrow rate
gcRate	FLOAT		0	expected overnight rate if general collateral usually FF overnight rate
borrowRate	FLOAT		0	expected overnight borrow rate special borrrow
stMinIVol	FLOAT		0	minimum atm implied volatility all expirations with a position
stMaxIVol	FLOAT		0	maximum atm implied volatility all expirations with a position
earnMult	FLOAT		0	expected earnings multiplier affects some risk slides 10 80
earnMultErr	FLOAT		0
baseVol	FLOAT		0	from earn mult fit 075 yrs
tailVol	FLOAT		0	weighted toward max expiration
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3
accnt	4
tradeDate	5
clientFirm	6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgSymbolRiskDetailV5` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm',
    `clientTags` TEXT NOT NULL DEFAULT '' COMMENT '(optional) user defined account tag(s) (eg. tag,tag,tag,tag) [max of 10,000 total chars (including seps)]' CHECK(LENGTH(clientTags) <= 10000),
    `stkStatus` ENUM('Hold','TwoWay','DayHold') NOT NULL DEFAULT 'Hold' COMMENT 'Stock trading status indicator (user controlled;used by the HedgeTool and by trade controllers) [SymbolControl.stkStatus]',
    `optStatus` ENUM('Hold','TwoWay','BuyOnly','SellOnly','CloseOnly','CloseNow','CloseRisk','BuyCloseOnly','SellCloseOnly') NOT NULL DEFAULT 'Hold' COMMENT 'Option trading status indicator (user controlled;only used by trade controllers) [SymbolControl.optStatus]',
    `riskClass` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Symbol Risk Class Code (user supplied) [SymbolControl.riskClass]',
    `theoModel` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned theo model tag (associated with user supplied theo surfaces) [AccountConfig.theoModel]',
    `theoModel2` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned theo model#2 tag (associated with user supplied theo surfaces) [AccountConfig.theoModel2]',
    `hedgeDeltaRule` ENUM('None','IVol','IvS','TVol','TvS','Binary','IvS_25','IvS_50','IvS_75','TvAll','TvAllS') NOT NULL DEFAULT 'None' COMMENT 'HedgeDelta Source (IVol = use SR implied surface (sticky strike), IvS = use SR surface (sticky delta), TVol = use user supplied theo surface (sticky strike), TvS = use user supplied theo surface and atm veSlope (sticky delta)) [AccountConfig.hedgeDelta]',
    `holdReason` ENUM('None','BadData','CorpAction','PendDeal','PendEvent','ExtTrade','LowPrice','PendEarn','DealRumour','BadDiv','Watch','NewSym','NoLoc','NegPerf','NegEdge') NOT NULL DEFAULT 'None' COMMENT 'User supplied description (only informational) [SymbolControl.holdReason]',
    `binaryDays` FLOAT NOT NULL DEFAULT 0 COMMENT 'Fractional days [0 - 5.0] prior to expiration after which hedgeDeltas become binary [-1.0, -0.5, 0, +0.5, +1.0] [SymbolControl.binaryDays]',
    `ctrlUpdate` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'last update dttm of SymbolControl record [SymbolControl.timestamp]',
    `periodEndTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'DTTM of the end of the current trading period',
    `symbolType` ENUM('None','Equity','ADR','ETF','CashIndex','MutualFund','ShortETF','Future','Bond','DepReceipts','PreferredSec','PreferenceShare','StructuredProd','StapledSec','TradeableRights','Unit','Warrant','WhenIssued','ForeignIssue') NOT NULL DEFAULT 'None' COMMENT 'SymbolType (Equity, ETF, ShortETF, ADR, CashIndex, FutureComplex)',
    `name` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Symbol name/description',
    `industry` INT NOT NULL DEFAULT 0,
    `sector` VARCHAR(16) NOT NULL DEFAULT '',
    `beta` FLOAT NOT NULL DEFAULT 0 COMMENT 'beta (usually beta to SPX; see AccountConfig.betaSource)',
    `betaSource` ENUM('None','betaSPX','betaQQQ','betaIWM','clientBeta') NOT NULL DEFAULT 'None',
    `stkVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'trailing 30 day average daily stock volume',
    `optVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'trailing 30 day average daily option volume',
    `tapeCode` ENUM('None','A','B','C') NOT NULL DEFAULT 'None' COMMENT 'market data tape code',
    `marginType` ENUM('None','NMS_Equity','NMS_Index','NMS_Medium') NOT NULL DEFAULT 'None' COMMENT 'margin slide type: NMS_Equity = +/- 15%, NMS_Index = -8%/+6%, NMS_Medium = +/-10%',
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `dAmt` FLOAT NOT NULL DEFAULT 0 COMMENT '$amount of the next expected dividend',
    `dDays` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to next expected dividend (negative indicates days from a recent ex-date)',
    `eDays` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to next expected earnings (negative indicates days from a recent announcement)',
    `locateQuan` INT NOT NULL DEFAULT 0 COMMENT 'starting locate quantity in this symbol befores trades',
    `availableLocateQuan` INT NOT NULL DEFAULT 0 COMMENT 'available equity locate quantity in this symbol after trades',
    `gcFlag` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'is this symbol a general collateral name (no special borrow rate)',
    `gcRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'expected overnight rate if general collateral (usually FF overnight rate)',
    `borrowRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'expected overnight borrow rate (special borrrow)',
    `stMinIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'minimum atm implied volatility (all expirations with a position)',
    `stMaxIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'maximum atm implied volatility (all expirations with a position)',
    `earnMult` FLOAT NOT NULL DEFAULT 0 COMMENT 'expected earnings multiplier (affects some risk slides) [1.0 - 8.0]',
    `earnMultErr` FLOAT NOT NULL DEFAULT 0,
    `baseVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'from earn mult fit (<0.75 yrs)',
    `tailVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'weighted toward max expiration',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`accnt`,`tradeDate`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SymbolRiskDetail records contain semi-static markup detail for SymbolRiskSummary records.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `accnt`,
    `tradeDate`,
    `clientFirm`,
    `clientTags`,
    `stkStatus`,
    `optStatus`,
    `riskClass`,
    `theoModel`,
    `theoModel2`,
    `hedgeDeltaRule`,
    `holdReason`,
    `binaryDays`,
    `ctrlUpdate`,
    `periodEndTime`,
    `symbolType`,
    `name`,
    `industry`,
    `sector`,
    `beta`,
    `betaSource`,
    `stkVolume`,
    `optVolume`,
    `tapeCode`,
    `marginType`,
    `pointCurrency`,
    `dAmt`,
    `dDays`,
    `eDays`,
    `locateQuan`,
    `availableLocateQuan`,
    `gcFlag`,
    `gcRate`,
    `borrowRate`,
    `stMinIVol`,
    `stMaxIVol`,
    `earnMult`,
    `earnMultErr`,
    `baseVol`,
    `tailVol`,
    `timestamp`
FROM `SRRisk`.`MsgSymbolRiskDetailV5`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='SymbolRiskDetailV5' ORDER BY ordinal_position ASC;